{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "e45f9b60-cd6b-4c15-958f-1feca5438128",
   "metadata": {},
   "source": [
    "# Pandas Query Engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "119eb42b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import logging\n",
    "import sys\n",
    "from IPython.display import Markdown, display\n",
    "\n",
    "import pandas as pd\n",
    "from llama_index.query_engine import PandasQueryEngine\n",
    "\n",
    "\n",
    "logging.basicConfig(stream=sys.stdout, level=logging.INFO)\n",
    "logging.getLogger().addHandler(logging.StreamHandler(stream=sys.stdout))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "5ece7d73-0f67-4ff5-95e5-249a25bd118c",
   "metadata": {},
   "source": [
    "### Let's start on a Toy DataFrame\n",
    "\n",
    "Very simple dataframe containing city and population pairs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1484fe58-4853-4a76-bffc-435a9cce3e2e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Test on some sample data\n",
    "df = pd.DataFrame(\n",
    "    {\"city\": [\"Toronto\", \"Tokyo\", \"Berlin\"], \"population\": [2930000, 13960000, 3645000]}\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4fea2edb-b3d4-4313-a656-d6edb00d93c0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query_engine = PandasQueryEngine(df=df, verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "451836bc-b073-4838-8ab8-3def7d2c4d9d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "> Pandas Instructions:\n",
      "```\n",
      "\n",
      "df['city'][df['population'].idxmax()]\n",
      "```\n",
      "> Pandas Output: Tokyo\n"
     ]
    }
   ],
   "source": [
    "response = query_engine.query(\n",
    "    \"What is the city with the highest population?\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4253d4c3-f3e5-4779-bcd1-2e6e2818305f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "<b>Tokyo</b>"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(Markdown(f\"<b>{response}</b>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "5e10b7da-b355-49b2-9f80-f17541d4f850",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "df['city'][df['population'].idxmax()]\n"
     ]
    }
   ],
   "source": [
    "# get pandas python instructions\n",
    "print(response.metadata[\"pandas_instruction_str\"])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "1de5eaf3-6129-47b1-b630-faf9138a04c5",
   "metadata": {},
   "source": [
    "### Analyzing the Titanic Dataset\n",
    "\n",
    "The Titanic dataset is one of the most popular tabular datasets in introductory machine learning\n",
    "Source: https://www.kaggle.com/c/titanic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "809f18c8-e38b-449e-b5ee-c2ea700f8698",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"../data/csv/titanic_train.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "fb1758de-6310-4ed5-ae02-2dbf50d2c55f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query_engine = PandasQueryEngine(df=df, verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f9dd658d-b62c-4e3b-aee9-0a06f57de032",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "> Pandas Instructions:\n",
      "```\n",
      "df['survived'].corr(df['age'])\n",
      "```\n",
      "> Pandas Output: -0.07722109457217768\n"
     ]
    }
   ],
   "source": [
    "response = query_engine.query(\n",
    "    \"What is the correlation between survival and age?\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "60474389-341b-4187-87b2-83811546dcea",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "<b>-0.07722109457217768</b>"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(Markdown(f\"<b>{response}</b>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "af999a1f-fea6-4734-82e6-4450f1a06a3b",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df['survived'].corr(df['age'])\n"
     ]
    }
   ],
   "source": [
    "# get pandas python instructions\n",
    "print(response.metadata[\"pandas_instruction_str\"])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "llama",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
